package com.ssyt.util;

import java.sql.*;

public class Test {
	public static void main(String[] args) {
      new Test().showTree();
	}

	/**
	 * 首先找到主贴 在找到它的下一个的下一个直至最后个 也就是数据库中表的pid 首先连接数据库，通过参数的形式传送给他 conn 为数据库连接 id
	 * 为第一个主贴， levle 为子贴缩进
	 */
	private void tree(Connection conn, int id, int levle) {
		PreparedStatement pst = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM article WHERE pid=?";
		StringBuffer sb=new StringBuffer("");
		for (int i = 0; i < levle; i++) {
			sb.append("      ");
		}
		try {

			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			while (rs.next()) {
				// 打印主贴内容
				System.out.println(sb+ rs.getString("cont")+rs.getInt("id")+rs.getInt("pid"));
             //递归调用自身找到它的下一个帖子
				if(rs.getInt("islevel") != 0)
					tree(conn, rs.getInt("id"), levle + 1);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pst != null) {
					pst.close();
					pst = null;
				}
				

			} catch (SQLException e) {
				e.printStackTrace();

			}

		}

	}

	// 展示查询内容以树的形式
	public void showTree() {

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager
					.getConnection("jdbc:mysql://127.0.0.1:3306/bbs?useUnicode=true&characterEncoding=UTF-8","root","123456");
		   //查询数据库字段pid为帖子的等级 0为第一个人发表的1为回复0的 2为回复1的
			pst = conn.prepareStatement("SELECT * FROM article WHERE pid=0");
			rs = pst.executeQuery();
			while (rs.next()) {
				// 打印主贴内容pid为0为主贴
				System.out.println(rs.getString("cont"));
				//调用tree 该ID为主贴ID
                tree(conn, rs.getInt("id"), 1);
			}
		
		
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pst != null) {
					pst.close();
					pst = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}